# -*- coding: utf-8 -*-
"""
Created on Thu Sep 24 16:17:39 2020

@author: zhangjinyi
"""

import pandas as pd
import numpy as np
#import sqlite3 as sq3
from base import sql_engine,ts_pro
from dateutil.parser import parse
from datetime import datetime,timedelta
pro=ts_pro()
engine = sql_engine()

#获取交易日历
cals=pro.trade_cal(exchange='SSE')
cals=cals[cals.is_open==1].cal_date.values

def download_data(start='20100101',table_name='holder_data'):
    dates=cals[np.where(cals>start)]
    for d in dates:
        try:
            df = pro.stk_holdertrade(ann_date=d)
            df.to_sql(table_name,engine,index=False,if_exists='append')
        except:
            continue
    
#获取最新交易日期
def get_trade_date():
    #获取当天日期时间
    d0=datetime.now()
    if d0.hour>18:
        d=d0.strftime('%Y%m%d')
    else:
        d=(d0-timedelta(1)).strftime('%Y%m%d')
    while d not in cals:
        d1=parse(d)
        d=(d1-timedelta(1)).strftime('%Y%m%d')
    return d

#每日行情数据：table_name='daily_data'

def update_holderdata(table_name='holder_data'):
    d0=pd.read_sql(f"select max(ann_date) from {table_name}",engine)
    d0=d0.values[0][0]
    d1=get_trade_date()
    n0=np.argwhere(cals==d0)[0][0]+1
    n1=np.argwhere(cals==d1)[0][0]+1
    dates=cals[n0:n1]
    if len(dates)>0:
        for d in dates:
            try:
                df = pro.stk_holdertrade(ann_date=d)
                df.to_sql(table_name,engine,index=False,if_exists='append')
            except:
                continue
        print(f"数据库已更新至{dates[-1]}日数据")
    else:
        print("数据已经是最新的！")

if __name__ == "__main__":
    download_data()
